package com.gome.axwl.aoc.web.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;




/**
 * 导入Excel文件（支持“XLS”和“XLSX”格式）
 * 
 */
public class ExcelUtil {
    
    private static ExcelUtil UTIL = new ExcelUtil();

    /**
     * 
     * @param inputStream
     *            文件流
     * @param beanClass
     *            需要转换的对象
     * @param isXlsx
     *            是否是xlsx文件
     * @return
     * @throws ExcelException
     */
    public static <T> List<T> file2Data(InputStream inputStream, Class<T> beanClass, boolean isXlsx) throws ExcelException {
        return file2Data(inputStream, 0, 0, beanClass, isXlsx);
    }

    /**
     * 
     * @param inputStream
     *            文件流
     * @param sheetIndex
     *            表单序号，从0开始
     * @param headerNum
     *            表头行号，从0开始
     * @param beanClass
     *            需要转换的对象
     * @param isXlsx
     *            是否是xlsx文件
     * @return
     * @throws ExcelException
     */
    public static <T> List<T> file2Data(InputStream inputStream, int sheetIndex, int headerNum, Class<T> beanClass, boolean isXlsx)
            throws ExcelException {
        Workbook workbook = null;
        try {
            if (isXlsx) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                workbook = new HSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            throw UTIL.new ExcelException(ExcelError.FILE_NOT_EXIST, e);
        } finally {
            closeWorkbook(workbook);
        }
        if (workbook.getNumberOfSheets() < sheetIndex) {
            closeWorkbook(workbook);
            throw UTIL.new ExcelException(ExcelError.SHEEL_ERROR);
        }
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        int lastRow = sheet.getLastRowNum();
        List<T> result = new ArrayList<T>(lastRow - headerNum);
        List<String> fieldNames = new ArrayList<String>();
        for (int rowIndex = headerNum; rowIndex <= lastRow; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if(row == null){
                continue;
            }
            int firstColumn = 0;
            int lastColumn = row.getLastCellNum();
            T bean = null;
            try {
                bean = beanClass.newInstance();
            } catch (Exception e) {
                closeWorkbook(workbook);
                throw UTIL.new ExcelException(ExcelError.CANNOT_NEW_ENTITY, e);
            }
            for (int columnIndex = firstColumn; columnIndex < lastColumn; columnIndex++) {
                Cell cell = row.getCell(columnIndex);
                if (cell == null) {
                    continue;
                }
                Object value = getCellValue(cell);
                if (rowIndex == headerNum) {
                    String header = value + "";
                    int index = header.indexOf("#");
                    if (index < 0) {
                        closeWorkbook(workbook);
                        throw UTIL.new ExcelException(ExcelError.ERROR_MOULD);
                    }
                    String fieldName = header.substring(index + 1);
                    fieldNames.add(fieldName);
                    continue;
                }
                BeanUtil.setFieldValue(bean, fieldNames.get(columnIndex - firstColumn), value);
            }
            if (rowIndex != headerNum) {
                result.add(bean);
            }
        }
        closeWorkbook(workbook);
        return result;
    }

    private static void closeWorkbook(Workbook workbook) {
        if (workbook != null) {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 提取单元格中的内容信息
     * 
     * @param cell
     * @return
     */
    private static Object getCellValue(Cell cell) {
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        }
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
//            String doubleStr = cell.getNumericCellValue() + "";
//            String intStr = doubleStr.replace(".0", "");
            DecimalFormat df = new DecimalFormat("#");
            return df.format(cell.getNumericCellValue());
        }
        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return cell.getBooleanCellValue();
        }
        if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
            return cell.getErrorCellValue();
        }
        if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getCellFormula();
        }
        return null;
    }

    /**
     * Excel异常
     * 
     * @author Dell
     *
     */
    public class ExcelException extends Exception {

        /**
             * 
             */
        private static final long serialVersionUID = 1L;

        private int code;
        private String desc;

        public ExcelException(ExcelError error) {
            super();
            this.code = error.getCode();
            this.desc = error.getDesc();
        }

        public ExcelException(ExcelError error, Throwable e) {
            super(e);
            this.code = error.getCode();
            this.desc = error.getDesc();
        }

        public int getCode() {
            return code;
        }

        public void setCode(int code) {
            this.code = code;
        }

        public String getDesc() {
            return desc;
        }

        public void setDesc(String desc) {
            this.desc = desc;
        }
    }

    /**
     * Excel错误码
     */
    public enum ExcelError {
        SHEEL_ERROR(8000, "表单索引出错，没有指定的表单"), FILE_NOT_EXIST(8001, "Excel文件不存在"), CANNOT_NEW_ENTITY(8002,
                "对象无法创建，缺少无参构造器"), ERROR_MOULD(10022, "模板错误，请核对后重新上传");
        private int code;
        private String desc;

        private ExcelError(int code, String desc) {
            this.code = code;
            this.desc = desc;
        }

        public int getCode() {
            return code;
        }

        public String getDesc() {
            return desc;
        }
    }
    /***
     * 
     * @param sheetName
     * @param title
     *            excel每一列的标题数组
     * @param filed
     *            数据字段名数组
     * @param data
     *            数据。map中的key与filed数组中的值对应
     * @param wb
     * @return
     */
    public static XSSFWorkbook getXSSFWorkbook(String sheetName, String[] title, String[] filed, List<Map<String, Object>> data,
            XSSFWorkbook wb) {
        // 第一步，创建一个workbook，对应一个Excel文件
        if (wb == null) {
            wb = new XSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行
        XSSFRow row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        XSSFDataFormat format = wb.createDataFormat();  
        style.setDataFormat(format.getFormat("@"));  
        XSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        // 添加数据
        for (int i = 0; i < data.size(); i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < filed.length; j++) {
                row.createCell(j).setCellValue(getValue(data.get(i).get(filed[j])));
            }
        }
        return wb;
    }

    private static String getValue(Object o) {
        return (o == null) ? "" : o.toString();
    }

    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    public static void main(String[] args)  {
//        List<Importor> data = ExcelUtil.file2Data(new FileInputStream("D:/测试导入.xlsx"), 0, 0, Importor.class, true);
//        System.out.println(data.size());
//        System.out.println(data);
//        System.exit(0);
    	
    	DecimalFormat df = new DecimalFormat("#");
  	  String  value=String.valueOf(df.format(123));
  	  System.out.println(value);
    }
}
